/*
 *所有关于zpsc_report类的业务代码应在此处编写
*可使用repository.调用常用方法，获取EF/Dapper等信息
*如果需要事务请使用repository.DbContextBeginTransaction
*也可使用DBServerProvider.手动获取数据库相关信息
*用户信息、权限、角色等使用UserContext.Current操作
*zpsc_reportService对增、删、改查、导入、导出、审核业务代码扩展参照ServiceFunFilter
*/
using VOL.Core.BaseProvider;
using VOL.Core.Extensions.AutofacManager;
using VOL.Entity.DomainModels;
using System.Linq;
using VOL.Core.Utilities;
using System.Linq.Expressions;
using VOL.Core.Extensions;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.AspNetCore.Http;
using VOL.System.IRepositories;
using VOL.Core.EFDbContext;
using VOL.Core.DBManager;
using Confluent.Kafka;
using System.Data;
using System;
using Newtonsoft.Json;

namespace VOL.System.Services
{
    public partial class zpsc_reportService
    {
        private readonly IHttpContextAccessor _httpContextAccessor;
        private readonly Izpsc_reportRepository _repository;//访问数据库

        [ActivatorUtilitiesConstructor]
        public zpsc_reportService(
            Izpsc_reportRepository dbRepository,
            IHttpContextAccessor httpContextAccessor
            )
        : base(dbRepository)
        {
            _httpContextAccessor = httpContextAccessor;
            _repository = dbRepository;
            //多租户会用到这init代码，其他情况可以不用
            //base.Init(dbRepository);
        }


        public string GetZPSCReport()
        {
            string message = "aaa";
            //string sql = "select * from NewTable";
            var dapper = DBServerProvider.GetSqlDapper("report");
            var dapper2 = DBServerProvider.SqlDapperPgSql;

            string sql = "select * from \"Public\".mes_data where creator = '管理员'";
            var list = dapper2.QueryList<NewTable>(sql, null);
            app_data ad1 = JsonConvert.DeserializeObject<app_data>(list[0].app_data);
            return ad1.sex;
            //return list[0].creator;

        }


        public string GetPGTest()
        {

            string sql = $@"SELECT qrcode,cycle_name,relation_ids,department,parts_id1s,serial_no,parts_id2,create_person,create_time FROM (       
                                with temp1 as(SELECT            t2.parts_id2 as parts_id2,
                                    t1.cycle_name as cycle_name,
                                    string_agg(t1.serial_no::text,',' order by t1.serial_no asc) as serial_no,
                                    string_agg(t2.id::text,',' order by t2.id asc) as relation_ids,
                                    string_agg(parts_id1::text,',' order by parts_id1 asc) as parts_id1s,
                                    (ARRAY_AGG(distinct t2.action_data->>'create_person'::text))[1] as create_person,
                                    (ARRAY_AGG(distinct t2.action_data->>'create_time'::text))[1] as create_time
                                FROM
                                    mes_wip_parts_relation AS t2
                                    LEFT JOIN mes_wip_parts AS t1 ON t1.ID = t2.parts_id1
                                    LEFT JOIN ghp_print_label_info AS t3 ON t2.parts_id2 = t3.ID
                                    --where t1.tags != 'cancel'
                                group by
                                    parts_id2,
                                    cycle_name
                                )
                                select
                                    temp1.*,
                                    t1.extra_data ->> 'department' as department,
                                    t2.qrcode
                                    from temp1
                                left join sys_user AS t1 ON temp1.create_person = t1.fullname
                                left join ghp_print_label_info AS t2 ON temp1.parts_id2 = t2.ID
                                ) _TEMP_TABLE_ WHERE ((((create_time >= '2024-07-01 07:00:00' and department = '冲压装配部')))) ORDER BY parts_id2 ";
            DataTable dt = DBServerProvider.QueryPGSql(sql);
            return dt.Rows[0]["qrcode"].ToString();
        }

        public class NewTable
        {
            public DateTime create_time { get; set; }
            public string creator { get; set; }
            public DateTime modify_time { get; set; }
            public string modifier { get; set; }
            public string app_data { get; set; }
        }

        public class app_data
        {
            public string name { get; set; }
            public string sex { get; set; }
            public int height { get; set; }
            public int weight { get; set; }
        }
  }
}
